package com.crm.dao;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.beans.EnquiryBean;
import com.crm.entity.Quotation;
import com.crm.entity.SupplyOrder;
import com.crm.util.ApplicationContext;

public class SupplyOrderDAO {
	protected final Log logger = LogFactory.getLog(getClass());

	public  void insert(SupplyOrder ss)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		 tx = session.beginTransaction();
		     session.saveOrUpdate(ss);
		     tx.commit();
		     return ;
		}
		catch (Exception e) {
		  if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}	
	public  void insertList(List<SupplyOrder> lso)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			for(SupplyOrder so:lso){
				session.saveOrUpdate(so);
			}
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}	
	public  List<SupplyOrder> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<SupplyOrder> custObjs=new ArrayList<SupplyOrder>();
		     List objs=null;
		     Query query=session.createQuery("from SupplyOrder where business_id=:busid");
		     query.setInteger("busid", busid);
		     objs=query.list();
		     for(Object o:objs){
		    	 custObjs.add((SupplyOrder)o);
		     }
		     return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> getAllSO(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from SupplyOrder where business_id=:busid";
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("busid", busid);
		     List<SupplyOrder> q=qry.list();
		     return q;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}


	

	public  List<Quotation> getQuotNum(int refNum,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from Quotation where enqid=:rfNo and business_id=:busid";
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("rfNo", refNum);
		     qry.setInteger("busid", busid);
		     List<Quotation> q=qry.list();
		     return q;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}


	public  SupplyOrder findByQutId(int q,int prt,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from SupplyOrder where quotid=:quot and prtNum=:part and business_id=:busid"; 
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("quot", q);
		     qry.setInteger("part", prt);
			qry.setInteger("busid", busid);
			Object o=qry.uniqueResult();
			SupplyOrder s=(SupplyOrder)o;
			return s;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> findByQIdPrtId(int q,int prt,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from SupplyOrder where quotid=:quot and prtid=:part and business_id=:busid"; 
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("quot", q);
		     qry.setInteger("part", prt);
		     qry.setInteger("busid", busid);
		     Object o=qry.list();
		     List<SupplyOrder> s=(List<SupplyOrder>)o;
		     return s;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
		
	}


	public  Long getCount(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="select COUNT(*)+1 from SupplyOrder where business_id=:busid"; 
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("busid",busid);
		     Object o=qry.uniqueResult();
		     Long c=(Long)o;
		     return c;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}


	public  SupplyOrder getinvoice(int qn,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="from SupplyOrder  where quotid=:qut and business_id=:busid";
		     Query query=session.createQuery(querystr);		
		     query.setInteger("qut",qn);
		     query.setInteger("busid", busid);
		     List<SupplyOrder> sl=query.list();
		     Iterator itr=sl.iterator();
		     SupplyOrder so=null;
		     while(itr.hasNext()==true){
		    	 Object o=itr.next();
		    	 so=(SupplyOrder)o;
			
		     }
		     return so;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}


	public  Quotation getByQut(String qn,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from Quotation where quotid=:qut and business_id=:busid"; 
		     Query qry=session.createQuery(qryStr);
		     qry.setString("qut",qn);
		     qry.setInteger("busid", busid);
		     Object o=qry.uniqueResult();
		     Quotation s=(Quotation)o;
		     return s;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> getByQuotid(int busid,int quotid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qryStr="from SupplyOrder  where business_id=:busid and quotid=:quotid"; 
		     Query qry=session.createQuery(qryStr);
		     qry.setInteger("quotid",quotid);
		     qry.setInteger("busid",busid);
		     List<SupplyOrder> s=(List<SupplyOrder>)qry.list();
		     return s;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> findOrdersByYear(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<SupplyOrder> custObjs=new ArrayList<SupplyOrder>();
		     List objs=null;
		     Calendar currentDate = Calendar.getInstance();
		     SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
		     String dt = formatter.format(currentDate.getTime());
		     String[] temp=dt.split("-");
		     String year=temp[0];
		     String month=temp[1];
		     String date=temp[2];
		     Query query=session.createQuery("from SupplyOrder where date like'"+year+"%' and business_id=:busid");
		     query.setInteger("busid", busid);
		     objs=query.list();
		     for(Object o:objs){
		    	 custObjs.add((SupplyOrder)o);
		     }
		     return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> findOrdersByMonth(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<SupplyOrder> custObjs=new ArrayList<SupplyOrder>();
		     List objs=null;
		     Calendar currentDate = Calendar.getInstance();
		     SimpleDateFormat formatter= 
		    	 new SimpleDateFormat("yyyy-MM-dd");
		     String dt = formatter.format(currentDate.getTime());
		     String[] temp=dt.split("-");
		     Query query=session.createQuery("from SupplyOrder where date like'%-"+temp[1]+"-%' and business_id=:busid");
		     query.setInteger("busid", busid);
		     logger.info(query);
		     objs=query.list();
		     for(Object o:objs){
		    	 custObjs.add((SupplyOrder)o);
		     }
		     return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<SupplyOrder> findOrdersByWeek(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<SupplyOrder> custObjs=new ArrayList<SupplyOrder>();
		     List objs=null;
		     Calendar currentDate = Calendar.getInstance();
		     SimpleDateFormat formatter= 
		    	 new SimpleDateFormat("yyyy-MM-dd");
		     String now = formatter.format(currentDate.getTime());
		     Calendar cal = Calendar.getInstance();
		     cal.add(Calendar.DATE, -7);
		     String dt=cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH) + 1)+"-"+cal.get(Calendar.DATE);
		     Query query=session.createQuery("from SupplyOrder where date between '"+dt+"' and '"+now+"' and business_id=:busid");
		     query.setInteger("busid", busid);
		     logger.info(query);
		     objs=query.list();
		     for(Object o:objs){
		    	 custObjs.add((SupplyOrder)o);
		     }
		     return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public double getCompanyBusiness(int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("select sum(prc_aftr_discount) from SupplyOrder where business_id=:busid");
			query.setInteger("busid",busid);
			double total=(Double)query.uniqueResult();
			return total;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public double getCompanyMonTO(int year,int month, int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("select sum(prc_aftr_discount) from SupplyOrder where business_id=:busid and date like '"+year+"%"+month+"%'");
			query.setInteger("busid",busid);
			double total=0;
			if(query.uniqueResult()!=null)
			total=(Double)query.uniqueResult();
			return total;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public  double getExecBusiness(int busid,String execName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select sum(prc_aftr_discount) from SupplyOrder where business_id=:busid and quotid in(select id from Quotation where enqid in(select id from Opportunity where exec_name=:execName ))";
			Query qry=session.createQuery(qstr);
			qry.setInteger("busid",busid);
			qry.setString("execName",execName);
			Double res=(Double) qry.uniqueResult();
			double business=0;
			if(res!=null){
				 business=res ;
			}
			
			return business ;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  double getExecBusinessByDate(int busid,String from,String to,String execName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select sum(prc_aftr_discount) from SupplyOrder where business_id=:busid and quotid in(select id from Quotation where enqid in(select id from Opportunity where exec_name=:execName )) and date between '"+from+"' and '"+to+"'";
			Query qry=session.createQuery(qstr);
			qry.setInteger("busid",busid);
			qry.setString("execName",execName);
			Double res=(Double) qry.uniqueResult();
			double business=0;
			if(res!=null){
				business=res ;
			}
			
			return business ;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  long getExecBusinessCount(int busid,String execName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select count(*) from SupplyOrder where business_id=:busid and quotid in(select id from Quotation where enqid in(select id from Opportunity where exec_name=:execName ))";
			Query qry=session.createQuery(qstr);
			qry.setInteger("busid",busid);
			qry.setString("execName",execName);
			long business=(Long) qry.uniqueResult();
			return business ;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public SupplyOrder findByPartIdBusId(int partid, int busid) throws HibernateException, Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="from SupplyOrder where business_id=:busid and prtid=:partid";
			Query qry=session.createQuery(qstr);
			qry.setInteger("busid",busid);
			qry.setInteger("partid", partid);
			SupplyOrder so=(SupplyOrder) qry.uniqueResult();
			return so;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public List<EnquiryBean> getBankGivenInfo( int busid) throws HibernateException, Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select op.enqnum as enqnum,op.ref_num as refno,op.enquirytype as type,so.bgdate as bgdate,so.bgedate as bgedate,so.invoice as invoice  from supply_order so,opportunity op,quotation quot where so.quotid=quot.id and quot.enqid=op.id and so.bgedate is not null and so.waredate is not null and so.business_id="+busid+" order by so.bgedate";
			SQLQuery qry=session.createSQLQuery(qstr);
			//qry.setInteger("busid",busid);
			qry.addScalar("enqnum",Hibernate.STRING);
			qry.addScalar("refno",Hibernate.STRING);
			qry.addScalar("type",Hibernate.STRING);
			qry.addScalar("bgdate",Hibernate.DATE);
			qry.addScalar("bgedate",Hibernate.DATE);
			qry.addScalar("invoice",Hibernate.STRING);
			List<Object> qresult=(List<Object>)qry.list();
			List<EnquiryBean> oplist=new ArrayList<EnquiryBean>();
			Iterator itr= qresult.iterator();
			while(itr.hasNext()){
				Object[] obj=(Object [])itr.next();
				EnquiryBean eb=new EnquiryBean((String)obj[0],(String)obj[1],(String)obj[2],(Date)obj[3],(Date)obj[4],(String)obj[5]);
				oplist.add(eb);
			}
			
			return oplist;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public List<EnquiryBean> getBankAlerts( int busid) throws HibernateException, Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select op.enqnum as enqnum,op.ref_num as refno,op.enquirytype as type,so.bgdate as bgdate,so.bgedate as bgedate,so.invoice as invoice  from supply_order so,opportunity op,quotation quot where so.quotid=quot.id and quot.enqid=op.id and so.bgedate is not null and so.waredate is not null and so.bgedate>=curDate() and so.business_id="+busid+" order by so.bgedate";
			SQLQuery qry=session.createSQLQuery(qstr);
			//qry.setInteger("busid",busid);
			qry.addScalar("enqnum",Hibernate.STRING);
			qry.addScalar("refno",Hibernate.STRING);
			qry.addScalar("type",Hibernate.STRING);
			qry.addScalar("bgdate",Hibernate.DATE);
			qry.addScalar("bgedate",Hibernate.DATE);
			qry.addScalar("invoice",Hibernate.STRING);
			List<Object> qresult=(List<Object>)qry.list();
			List<EnquiryBean> oplist=new ArrayList<EnquiryBean>();
			Iterator itr= qresult.iterator();
			while(itr.hasNext()){
				Object[] obj=(Object [])itr.next();
				EnquiryBean eb=new EnquiryBean((String)obj[0],(String)obj[1],(String)obj[2],(Date)obj[3],(Date)obj[4],(String)obj[5]);
				oplist.add(eb);
			}
			
			return oplist;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	//here we have to get invoice due date is near plz change this query for usage
	public List<EnquiryBean> getInvoiceDueInfo( int busid) throws HibernateException, Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="select op.enqnum as enqnum,op.ref_num as refno,op.enquirytype as type,so.bgdate as bgdate,so.bgedate as bgedate,so.invoice as invoice  from supply_order so,opportunity op,quotation quot where so.quotid=quot.id and quot.enqid=op.id and so.bgedate is not null and so.waredate is not null and so.bgedate>=curDate() and so.business_id="+busid+" order by so.bgedate";
			SQLQuery qry=session.createSQLQuery(qstr);
			//qry.setInteger("busid",busid);
			qry.addScalar("enqnum",Hibernate.STRING);
			qry.addScalar("refno",Hibernate.STRING);
			qry.addScalar("type",Hibernate.STRING);
			qry.addScalar("bgdate",Hibernate.DATE);
			qry.addScalar("bgedate",Hibernate.DATE);
			qry.addScalar("invoice",Hibernate.STRING);
			List<Object> qresult=(List<Object>)qry.list();
			List<EnquiryBean> oplist=new ArrayList<EnquiryBean>();
			Iterator itr= qresult.iterator();
			while(itr.hasNext()){
				Object[] obj=(Object [])itr.next();
				EnquiryBean eb=new EnquiryBean((String)obj[0],(String)obj[1],(String)obj[2],(Date)obj[3],(Date)obj[4],(String)obj[5]);
				oplist.add(eb);
			}
			
			return oplist;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
}
